﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Adams.Admin.DataAccess.Files
{
    public static class Index
    {
        public static Adams.Admin.DataTransfer.Files.Index.PageLoad PageLoad()
        {
            var data = new Adams.Admin.DataTransfer.Files.Index.PageLoad();
            
            return data;
        }

        public static Adams.Admin.DataTransfer.Files.Index.GridLoad GridLoad(DataTransfer.GridLoadInput gridinput)
        {
            var data = new Adams.Admin.DataTransfer.Files.Index.GridLoad();
            data.Records = new Shared.Entities.Files.FileCollection();

            string filter = string.IsNullOrWhiteSpace(gridinput.Search) ? string.Empty : "FREETEXT ((Url,Content), '" + gridinput.Search.Replace("'", "''") + "' )";

            string dbq = DB.BuildPagingQuery(gridinput.StartRow, gridinput.PageSize,
                "FileID,Url,ContentType,Size,LastModified",
                "Files.Files",
                filter, string.Empty, gridinput.Sort + " " + gridinput.SortDirection, "FileID");

            using (SqlDataReader dr = DB.RunDataReader(dbq, CommandType.Text))
            {
                dr.Read();
                data.TotalRecords = dr[0].DbInt();

                dr.NextResult();
                while (dr.Read())
                {
                    var File = new Shared.Entities.Files.File();
                    File.ID = dr["FileID"].DbInt();
                    File.Url = dr["Url"].DbString();
                    File.ContentType = dr["ContentType"].DbString();
                    File.Size = dr["Size"].DbLong();
                    File.LastModified = dr["LastModified"].DbDate();
                    data.Records.Add(File);
                }
            }

            return data;
        }

        public static void Delete(int id)
        {
            DB.Run(@"
DELETE Files.Files
WHERE FileID = @FileID
", CommandType.Text,
                DB.CreateParameter("@FileID", id, SqlDbType.Int));
        }

        public static void Upload(Adams.Admin.DataTransfer.Files.Index.UploadedFile uploadedFile)
        {
            uploadedFile.Url = "/" + uploadedFile.Url.Trim('/');

            DB.Run(@"
UPDATE Files.Files SET Url = @Url, Size = @Size, ContentType = @ContentType, Content = @Content, Extension = @Extension, LastModified = GETUTCDATE()
WHERE Url = @Url

IF (@@ROWCOUNT = 0)
BEGIN
    INSERT INTO Files.Files (Url, Size, ContentType, Content, Extension, LastModified)
    VALUES (@Url, @Size, @ContentType, @Content, @Extension, GETUTCDATE())
END
", CommandType.Text,
                DB.CreateParameter("@Url", uploadedFile.Url),
                DB.CreateParameter("@Size", uploadedFile.Size),
                DB.CreateParameter("@ContentType", uploadedFile.ContentType),
                DB.CreateParameter("@Extension", uploadedFile.Extension),
                DB.CreateParameter("@Content", uploadedFile.Content, SqlDbType.VarBinary));
        }

        public static Adams.Shared.Entities.Files.File GetFile(int fileId)
        {
            var data = new Adams.Shared.Entities.Files.File();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT F.*
FROM Files.Files F
WHERE FileID = @FileID
", CommandType.Text,
    DB.CreateParameter("@FileID", fileId)))
            {
                dr.Read();
                data.ID = dr["FileID"].DbInt();
                data.Url = dr["Url"].DbString();
                data.ContentType = dr["ContentType"].DbString();
                data.Size = dr["Size"].DbLong();
                data.LastModified = dr["LastModified"].DbDate();
                data.Content = (byte[])dr["Content"];
            }

            return data;
        }
    }
}
